# -*- coding: utf-8 -*-
import urllib,urllib2,cookielib,re,os
from datetime import datetime
from win32com.client import Dispatch

# 去除list中重复的值
def f9(seq):
    # Not order preserving
    return {}.fromkeys(seq).keys()
        
# 判断是否是进口
def hasimport(cd):
    # 第8位为0是出口，1是进口
    return int(cd[8])
    
# 定义获取已经存在的报关单号的list函数
# 由于海关报关系统中的单子修改后日期会变更，查询当天时会把前几天的单子也查询出来，因此更改查询条件为报关单号是否存在
def getexistcdnos(cds):
    has_cdnos=[]
    # 加逗号和单引号分隔符
    cdnos_str="','".join(cds)
    # 前后各加上1个单引号，组成sql中in条件中的字符串
    cdnos_str="'"+cdnos_str+"'"
    print u"查询字符串：",cdnos_str
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    oRS.Open("select cd_no from cd where cd_no in ("+cdnos_str+")")                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        # print oRS.Fields("cd_no").Value
        has_cdnos.append(oRS.Fields("cd_no").Value)
        oRS.MoveNext()
    print u"查找到的报关单号：",has_cdnos
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    return has_cdnos

def gettodaycdnos(search_date):
    has_cdnos=[]
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    oRS.Open("select cd_no from cd where cd_date=#"+search_date+"#")                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        # print oRS.Fields("cd_no").Value
        has_cdnos.append(oRS.Fields("cd_no").Value)
        oRS.MoveNext()
    # print u"查找到的报关单号：",has_cdnos
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    return has_cdnos

    
# 获取委托人关联的模板费用，默认为托浦1
def gettmpfee():
    wtr='1'
    tmp={}
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn
    oRS.Open("select receive,pay from wtr left join tmp on wtr.tmp=tmp.id where wtr.id="+wtr)
    if not oRS.EOF:
        if oRS.Fields("receive").Value:
            tmp["cd_receive"]=str(oRS.Fields("receive").Value)
        else:
            tmp["cd_receive"]='0'
        if oRS.Fields("pay").Value:
            tmp["cd_pay"]=str(oRS.Fields("pay").Value)
        else:
            tmp["cd_pay"]='0'
    else:
        tmp["cd_receive"]='0'
        tmp["cd_pay"]='0'
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    return tmp
    
# 新增一个报关单
def addnewcdno(cd):
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
    oConn.Open()
    oCmd = Dispatch('ADODB.Command')
    oCmd.ActiveConnection = oConn
    cd_bwtr=str(cd['cd_bwtr'])
    cd_no=cd['cd_no']
    # 进口数据库设置为18，出口为16
    if hasimport(cd_no):
        category='18'
    else:
        category='16'
    cd_company=cd['cd_company']
    cd_voyage=cd['cd_voyage']
    cd_lading=cd['cd_lading']
    cd_verify=cd['cd_verify']
    cd_passno=cd['cd_passno']
    cd_orderno=cd['cd_orderno']
    tmpfee=gettmpfee()
    cd_receive=tmpfee['cd_receive']
    cd_pay=tmpfee['cd_pay']
    cd_boxno=cd['cd_boxno']
    # cd_memo=cd['cd_memo']
    oCmd.CommandText = "insert into cd(wtr,bwtr,category,cd_no,cd_date,cd_company,cd_voyage,cd_lading,cd_verify,cd_post,username,cd_passno,cd_orderno,cd_receive,cd_pay,cd_boxno) values(1,"+cd_bwtr+","+category+",'"+cd_no+"','"+search_date+"','"+cd_company+"','"+cd_voyage+"','"+cd_lading+"','"+cd_verify+"',1,1,'"+cd_passno+"','"+cd_orderno+"',"+cd_receive+","+cd_pay+",'"+cd_boxno+"')"
    #print oCmd.CommandText
    
    oCmd.Execute()
    
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn
    oRS.Open("select @@IDENTITY")
    re_id=oRS.getstring
    oRS.Close()
    oRS=None
    boxs=cd['boxs']
    for box in boxs:
        oCmd.CommandText = "insert into cdbox(cd,box) values("+re_id+",'"+box+"')"
        oCmd.Execute()
    oCmd.CommandText = "insert into record(re_table,re_id,re_operate,re_memo,username) values('cd',"+re_id+",'"+unicode('新增','utf8')+"','"+unicode('同步','utf8')+"',1)"
    oCmd.Execute()
    oCmd = None
    oConn.Close()
    oConn = None
    global UPDATECDS
    UPDATECDS=UPDATECDS+1
    
def updatelog(cdcount,updatecount):
    updatetime=datetime.strftime(datetime.now(),"%H:%M:%S")
    if cdcount:
        oConn = Dispatch('ADODB.Connection')
        oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
        oConn.Open()
        oCmd = Dispatch('ADODB.Command')
        oCmd.ActiveConnection = oConn
        ul_cont=u"系统同步信息："+updatetime+u" 同步来自宁波海关H2000报关单预录入系统 "+str(cdcount)+u" 单，系统新增 "+str(updatecount)+u" 条报关单"
        oCmd.CommandText="insert into ul(ul_date,ul_post,ul_cont) values('"+search_date+"',1,'"+ul_cont+"')"
        oCmd.Execute()
        oCmd = None
        oConn.Close()
        oConn = None
    
def getcdhtml(cd_no,sign=1):
    url='http://61.175.135.165/H2000EPORT/pre_bgd/bgd_print/normal_print.asp?pre_entry_id='+decode_cdno(cd_no)+'&soft_flag=&sign='+str(sign)
    # print url
    content=urllib2.urlopen(url).read()
    f=open(r'file\\'+cd_no+r'.'+str(sign)+r'.html','w')
    f.write(content)
    f.close()
    return content

def getboxhtml(cd_no):
    url='http://61.175.135.165/H2000EPORT/pre_bgd/bgd_print/jzx_print.asp?pre_entry_id='+decode_cdno(cd_no)
    content=urllib2.urlopen(url).read()
    f=open(r'file\\'+cd_no+r'.box.html','w')
    f.write(content)
    f.close()
    return content
    
'''
2011-11-29海关修改了报关单数字映射逻辑
原：    310120110518169826
链接：7me87peplqppqmpqpt
反转：tpqpmqppqlpep78em7

原：    310120110518169854
链接：gle87peplqppqmpqpt
tpqpmqppqlpep78elg

原：    310120110518170146
链接：7gpq4peplqppqmpqpt
反转：tpqpmqppqlpep4qpg7

发现是简单的数字替换，对应关系如下：
0123456789
qpmtgl74e8
'''

def decode_cdno(cdno):
    dict={0:'q',1:'p',2:'m',3:'t',4:'g',5:'l',6:'7',7:'4',8:'e',9:'8'}
    j=""
    for i in cdno:
        j=j+dict[int(i)]
    return j[::-1]
    
def getbwtr():
    bwtrs=[]
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    oRS.Open("select * from bwtr")                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        if oRS.Fields("uid").Value and oRS.Fields("pwd").Value:
                bwtr={}
                # print oRS.Fields("cd_no").Value
                bwtr["id"]=oRS.Fields("id").Value
                bwtr["bwtr"]=oRS.Fields("bwtr").Value
                bwtr["uid"]=oRS.Fields("uid").Value
                bwtr["pwd"]=oRS.Fields("pwd").Value
                bwtrs.append(bwtr)
        oRS.MoveNext()
    # print u"查找到的报关单号：",has_cdnos
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    return bwtrs
    
    
# ##########################################
# 主程序开始
# ##########################################

bwtrs=getbwtr()

for bwtr in bwtrs:
    print u'开始对被委托人',bwtr['bwtr'],u'抓取数据'
    # 定义查询的日期
    search_date=datetime.strftime(datetime.now(),"%Y-%m-%d")
    # 定义更新的报关单数量
    global UPDATECDS
    UPDATECDS=0
    # 使用cookie
    cookie_support= urllib2.HTTPCookieProcessor(cookielib.CookieJar())
    opener = urllib2.build_opener(cookie_support, urllib2.HTTPHandler)
    urllib2.install_opener(opener)
    # 登录信息
    login_post_data=urllib.urlencode({
        # 俞军老婆的帐号密码，甬鼎
        'uid':bwtr['uid'],
        'pwd':bwtr['pwd'],
        # 托浦的帐号密码，智达
        # 'uid':'6915',
        # 'pwd':'6915',
        'login':'yes'
    })
    # 登录的地址
    login_req = urllib2.Request(
        url = 'http://61.175.135.165/H2000EPORT/default.asp',
        data = login_post_data
    )
    login_result = urllib2.urlopen(login_req).read()
    # print result
    # 查询的信息
    first_post_data=urllib.urlencode({
        'Submit2':'查询',
        'b_date':search_date,
        'e_date':search_date,
        'i_e_type':'all',
        'pre_entry_id':'',
        # stats_code表示查询的单子所处的状态，G表示接单交单，all表示所有
        'stats_code':'G'
    })
    first_req = urllib2.Request(
        url = 'http://61.175.135.165/H2000EPORT/pre_bgd/entry_main/default.asp',
        data = first_post_data
    )
    # 得到查询结果
    first_result = urllib2.urlopen(first_req).read()
    # print result
    # pattern=r'\d{1}\/\d{1}'
    # pattern=r'\u5171.*\u6761'
    # pattern=r'共.*条'
    pattern=r'1\/\d+'
    search_pages=re.findall(pattern,first_result)
    print u"查询到共有页面：",search_pages
    search_cdnos=[]
    if search_pages:
        # 截取页面数量
        pages=search_pages[0][2:]
        print u"需要查询的总页数：",int(pages)
        for i in range(int(pages)):
            postdata=urllib.urlencode({
                'Submit2':'查询',
                # 查询日期为当天的单子
                'b_date':search_date,
                'e_date':search_date,
                'i_e_type':'all',
                'pre_entry_id':'',
                # 页面从1开始，所以i要+1
                'goto':i+1,
                'sbtpn':'GO',
                # stats_code表示查询的单子所处的状态，G表示接单交单，all表示所有
                'stats_code':'G'
            })
            req = urllib2.Request(
                url = 'http://61.175.135.165/H2000EPORT/pre_bgd/entry_main/default.asp',
                data = postdata
            )
            result = urllib2.urlopen(req).read()
            # 报关单号为18位数字组成
            pattern=r'(\d{18})'
            # 把找到的所有18位数字的报关单号添加进来，并过滤重复的报关单号码
            search_cdnos.extend(f9(re.findall(pattern,result)))
        # 再次过滤重复
        search_cdnos=f9(search_cdnos)
        print search_date,u"共查询到报关单号：",search_cdnos,len(search_cdnos)
        # 和数据库里存在的对比，去掉已经存在单子
        new_cdnos=list(set(search_cdnos)-set(getexistcdnos(search_cdnos)))
        print search_date,u"新产生的报关单号：",new_cdnos
        print u"手工添加的报关单号：",list(set(gettodaycdnos(search_date))-set(search_cdnos))

        # 循环查询新的报关单内容
        for x in new_cdnos:
            cd={}
            cd['cd_bwtr']=bwtr['id']
            sign=1
            # 详细报关单的地址
            # req = urllib2.Request(
                # sign参数是打印的报关单超过1页了，称为共几联
                # url = 'http://61.175.135.165/H2000EPORT/pre_bgd/bgd_print/normal_print.asp?pre_entry_id='+x+'&soft_flag=&sign=1')
            # 得到详细报关单文本
            # result = urllib2.urlopen(req).read()
            # fn=r'file\\'+x+r'.1.html'
            # print fn
            # f=open(fn,'w')
            # f.write(result)
            # f.close()
            result=getcdhtml(x)
            pattern=r'<td width="3%"    rowspan="2" align="center">'
            iName=len(re.findall(pattern,result))
            while iName==5:
                # 去抓下一页
                sign+=1
                if(os.path.isfile(r'file\\'+x+r'.'+str(sign)+r'.html')):
                    f=open(r'file\\'+x+r'.'+str(sign)+r'.html')
                    resultmore=f.read()
                    f.close()
                else:
                    resultmore=getcdhtml(x,sign)
                iName=len(re.findall(pattern,resultmore))
                if(iName==0):
                    os.remove(r'file\\'+x+r'.'+str(sign)+r'.html')
            print u"报关单号：",x
            cd["cd_no"]=x
            pattern=r'<div id="Layer2" style="position:absolute; width:232px; height:18px; z-index:2; left: 80px; top: 154px;">.+?<\/div>'
            cd_company=re.search(pattern,result).group(0)
            pattern=r'<.+?>'
            cd_company=re.sub(pattern,'',cd_company)
            print u"企业名称：",cd_company
            cd["cd_company"]=cd_company
            pattern=r'<div id="Layer2" style="position:absolute; width:191px; height:15px; z-index:2; left: 380px; top: 164px;">.+?</div>'
            cd_voyage=re.search(pattern,result).group(0)
            pattern=r'<.+?>'
            cd_voyage=re.sub(pattern,'',cd_voyage)
            print u"船名航次：",cd_voyage
            cd["cd_voyage"]=cd_voyage
            pattern=r'<div id="Layer2" style="position:absolute; width:143px; height:15px; z-index:2; left: 68px; top: 249px;">.+?</div>'
            try:
                # 核销单号可能会没有
                cd_verify=re.search(pattern,result).group(0)
                pattern=r'<.+?>'
                cd_verify=re.sub(pattern,'',cd_verify)
            except:
                cd_verify=""
            print u"核销单号：",cd_verify
            cd["cd_verify"]=cd_verify
            pattern=r'<div id="Layer2" style="position:absolute; width:160px; height:15px; z-index:2; left: 571px; top: 165px;">.+?</div>'
            cd_lading=re.search(pattern,result).group(0)
            pattern=r'<.+?>'
            cd_lading=re.sub(pattern,'',cd_lading)
            print u"提单号：",cd_lading
            cd["cd_lading"]=cd_lading
            pattern=r'B:\d{18}'
            try:
                # 通关单号可能会没有
                cd_passno=re.search(pattern,result).group(0)
                cd_passno=cd_passno[2:]
            except:
                cd_passno=""
            print u"通关单号：",cd_passno
            cd['cd_passno']=cd_passno
            pattern=r'<div id="Layer2" style="position:absolute; width:143px; height:15px; z-index:2; left: 77px; top: 282px;">.+?</div>'
            cd_orderno=re.search(pattern,result).group(0)
            pattern=r'<.+?>'
            cd_orderno=re.sub(pattern,'',cd_orderno)
            print u"订单号：",cd_orderno
            cd['cd_orderno']=cd_orderno
            # 去掉换行
            # pattern=r'\n'
            # cd_boxno=re.sub(pattern,'',result)
            # pattern=r'<div id="Layer2" style="position:absolute; width:175px; height:15px; z-index:2; left: 66px; top: 320px;">.+?</div>'
            # cd_boxno=re.search(pattern,cd_boxno).group(0)
            # pattern=r'[A-Z0-9]{11}'
            # cd_boxno=re.search(pattern,cd_boxno).group(0)
            # print u"箱号：",cd_boxno
            # cd['cd_boxno']=cd_boxno
            # cd['cd_memo']=""
            
            try:
                # 去掉换行
                pattern=r'\n'
                box=re.sub(pattern,'',result)
                pattern=r'<div id="Layer2" style="position:absolute; width:175px; height:15px; z-index:2; left: 66px; top: 320px;">.+?</div>'
                box=re.search(pattern,box).group(0)
                pattern=r'\(\d+\)'
                # box=re.search(pattern,box).group(0)
                cd_box=re.search(pattern,box)
                if(cd_box==None):
                    pattern=r'\* \d+'
                    cd_box=re.search(pattern,box).group(0)
                else:
                    cd_box=cd_box.group(0)
                pattern=r'\d+'
                cd_box=re.search(pattern,cd_box).group(0)
            except:
                cd_box=None
            cd['cd_boxno']=cd_box
            
            print cd
            boxhtml=getboxhtml(x)
            pattern=r'<strong>.+?</strong>'
            boxs=[]
            [boxs.append(re.sub(r'<.+?>','',x)) for x in re.findall(pattern,boxhtml)]
            cd['boxs']=boxs
            addnewcdno(cd)
        updatelog(len(new_cdnos),UPDATECDS)